import { Context } from "koa"
import User from "../../sql/module/user/user"
import { Success } from "../../code/http-exception"
import RoomNumber from "../../sql/module/area/room_number"
import sequelize from "sequelize"
import BlockNumber from "../../sql/module/area/block_number"
import Role from "../../sql/module/user/role"
import Menu from "../../sql/module/menu"
import Suggest from "../../sql/module/suggest/suggest"
import Log from "../../sql/module/log"
import { ListValidator } from "../../validators/public"
import { Op } from "sequelize"

const router: Api = new Map()
router.set('/getUserByArea', [async (ctx: Context) => {
    const countByArea_name = await User.count({
        attributes: ['Area_name'],
        group: 'Area_name'
    })
    const total = await User.count()
    const totalvisitor = await User.count({
        where: {
            ['$roles.uid$']: 1
        }, include: Role
    })
    throw new Success({ countByArea_name, total, totalvisitor })
}])

router.set('/getRoomByArea', [async (ctx: Context) => {
    const countByArea_name = await RoomNumber.count({
        attributes: [[sequelize.literal(`(
                    SELECT name
                    FROM areas
                    WHERE
                        RoomNumber.AreaId = areas.id
                )`), 'Area_name']],
        group: 'Area_name'
    })
    const total = await RoomNumber.count()
    throw new Success({ countByArea_name, total })
}])

router.set('/getBlockByArea', [async (ctx: Context) => {
    const countByArea_name = await BlockNumber.count({
        attributes: [[sequelize.literal(`(
                    SELECT name
                    FROM areas
                    WHERE
                        BlockNumber.AreaId = areas.id
                )`), 'Area_name']],
        group: 'Area_name'
    })
    const total = await BlockNumber.count()
    throw new Success({ countByArea_name, total })
}])

router.set('/getMenuByType', [async (ctx: Context) => {
    const countByType = await Menu.count({
        attributes: ["type"],
        group: 'type'
    })
    const total = await Menu.count()
    const roleTotal = await Role.count()
    throw new Success({ countByType, total, roleTotal })
}])

router.set('/getSuggestByTime', [async (ctx: Context) => {
    const countByTime = await Suggest.count({
        attributes: [[sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), '%Y-%m-%d'), "created_at"]],
        where: {
            SuggestId: null
        },
        group: 'created_at'
    })
    const total = await Suggest.count()
    throw new Success({ countByTime, total })
}])

router.set('/getLogByToday', [async (ctx: Context) => {
    const v = await new ListValidator().validate(ctx)
    const total = v.get('query.limit') * 1
    const list = await Log.findAll({
        limit: total,
        order: [
            ['createdAt', 'DESC']
        ]
    })
    throw new Success({ list, total })
}])
// 获取头像
router.set('/getAvatar', [async (ctx: Context) => {
    const avatar = await User.findAll({
        attributes: ['avatar', 'fullUrl'],
        where: {
            avatar: {
                [Op.ne]: null
            }
        },
        order: [
            ['createdAt', 'DESC']
        ],
        limit: 6
    })
    throw new Success(avatar)
}])

// 获取首页数据
router.set('/getDashboardAll', [async (ctx: Context) => {
    const countByUser = await User.count({
        attributes: ['Area_name'],
        group: 'Area_name'
    })
    const totalUser = await User.count()
    const totalVisitor = await User.count({
        where: {
            ['$roles.uid$']: 1
        }, include: Role
    })
    const countByMenu = await Menu.count({
        attributes: ["type"],
        group: 'type'
    })
    const totalMenu = await Menu.count()
    const totalRole = await Role.count()
    const countBySuggest = await Suggest.count({
        attributes: [[sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), '%Y-%m-%d'), "created_at"]],
        where: {
            SuggestId: null
        },
        group: 'created_at'
    })
    const countByRoom = await RoomNumber.count({
        attributes: [[sequelize.literal(`(
                    SELECT name
                    FROM areas
                    WHERE
                        RoomNumber.AreaId = areas.id
                )`), 'Area_name']],
        group: 'Area_name'
    })
    const countByBlock = await BlockNumber.count({
        attributes: [[sequelize.literal(`(
                    SELECT name
                    FROM areas
                    WHERE
                        BlockNumber.AreaId = areas.id
                )`), 'Area_name']],
        group: 'Area_name'
    })
    throw new Success({ countByUser, totalUser, totalVisitor, countByMenu, totalMenu, totalRole, countBySuggest, countByRoom, countByBlock })
}])

export { router }